-- 生成定制件物料的编码规则: D + 年份后2位 + 3位流水号
alter function func_czly_GeneCustomizedMtlNumber()
    returns @result table (
        FNumber varchar(20),
        FLot varchar(20)
    )
as 
begin

    declare 
    @year varchar(10)=right(convert(varchar(10), YEAR(getdate())), 2)
    ,@month varchar(10)=dbo.fun_BosRnd_addSpace(MONTH(getdate()), '', '', 2)
    ,@max_num int=1
    ,@number varchar(20)
    ,@lot varchar(20)

    -- 1、生成物料编号
    select @max_num=max(convert(int, right(FNumber, 3)))+1
    from (
        select FNumber from T_BD_Material where FNumber like 'D' + @year + '%'
        union all
        select F_PAEZ_MLWCPBMa from PAEZ_t_OutOfCataProdApply where F_PAEZ_MLWCPBMa like 'D' + @year + '%'
    )t

    if @max_num is null set @max_num=1

    -- format： D+YY(两位年份)+XXX(3位流水号)
    set @number = dbo.fun_BosRnd_addSpace(@max_num, 'D'+@year, '', 3)

    -- 2、获取批号
    set @max_num = 1 --流水号仅 01
    -- select @max_num=max(convert(int, right(F_PAEZ_SCPHa, 2)))+1 from PAEZ_t_OutOfCataProdApply 
    -- where LEFT(F_PAEZ_SCPHa, 2)=@year and RIGHT(LEFT(F_PAEZ_SCPHa, 4), 2)=@month
    -- if @max_num is null set @max_num = 1
    -- format： YY(两位年份)+MM(2位月份)+XX(2位流水号)
    set @lot = dbo.fun_BosRnd_addSpace(@max_num, @year+@month, '', 2)

    insert into @result values(@number, @lot)

    return 
end

-- select * from dbo.func_czly_GeneCustomizedMtlNumber()
